SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO

/*===================================BEGIN=================================*/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--CREATE TABLE CAMPAIGNS CONTACTS
IF ((SELECT COUNT(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CAMPAIGNS_CONTACTS]')) <= 0)
BEGIN
	CREATE TABLE [dbo].[CAMPAIGNS_CONTACTS](
		[ID] [uniqueidentifier] NOT NULL,
		[CONTACT_ID] [uniqueidentifier] NOT NULL,
		[CAMPAIGN_ID] [uniqueidentifier] NOT NULL,
		[CREATED_DATE] [datetime] NULL,
		[FINISHED] [bit] NULL,
		[STATUS] [int] NULL,
	 CONSTRAINT [PK_CAMPAIGNS_CONTACTS] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[CAMPAIGNS_CONTACTS]  WITH CHECK ADD  CONSTRAINT [FK_CAMPAIGNS_CONTACTS_CAMPAIGNS] FOREIGN KEY([CAMPAIGN_ID])
	REFERENCES [dbo].[CAMPAIGNS] ([ID])

	ALTER TABLE [dbo].[CAMPAIGNS_CONTACTS] CHECK CONSTRAINT [FK_CAMPAIGNS_CONTACTS_CAMPAIGNS]

	ALTER TABLE [dbo].[CAMPAIGNS_CONTACTS]  WITH CHECK ADD  CONSTRAINT [FK_CAMPAIGNS_CONTACTS_CONTACTS] FOREIGN KEY([CONTACT_ID])
	REFERENCES [dbo].[CONTACTS] ([ID])

	ALTER TABLE [dbo].[CAMPAIGNS_CONTACTS] CHECK CONSTRAINT [FK_CAMPAIGNS_CONTACTS_CONTACTS]
END
GO



/*===================================END=================================*/


IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'

-- Record the script update
INSERT INTO dbo.[SchemaChanges]
       ([MajorReleaseNumber]
       ,[MinorReleaseNumber]
       ,[BuildReleaseNumber]
       ,[RevisionReleaseNumber]
       ,[ScriptName]
       ,[DateApplied]
       ,[Description])
VALUES
       ('1'
       ,'00'
       ,'01'
       ,'003'
       ,'Upgrade-1.00.01.003.sql'	   
       ,GETDATE()
	   ,'CREATE CAMPAIGNS_CONTACTS table')
	   COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO        

